"""
显示格式化工具模块

负责将聚合统计分析的业务数据转换为表格、图表、Excel等多种显示格式，
实现业务逻辑与显示逻辑的完全分离。

Author: DEV_VISION Team
Date: 2025-01-27
Version: v1.0
"""

import logging
from typing import Dict, List, Any, Optional
from io import BytesIO
from flask import current_app

from backend.config.config_manager import ConfigManager


class AnalysisDisplayService:
    """
    显示格式化服务类
    
    负责将聚合统计分析的业务数据转换为各种显示格式：
    - 表格数据格式化（百分比计算、合计行生成）
    - 三层表头生成（动态生成复杂表头结构）
    - 图表数据转换（转换为Chart.js格式）
    - Excel导出格式化（单元格合并、格式设置）
    """
    
    def __init__(self, config_manager: ConfigManager):
        """
        初始化格式化服务
        
        Args:
            config_manager: 配置管理器实例，确保三层架构合规
        """
        self.config_manager = config_manager
        self.logger = logging.getLogger(__name__)
        self._load_config()
    
    def _load_config(self):
        """加载格式化配置"""
        try:
            # 通过配置管理器获取配置，避免直接导入constants_v2
            self.field_display_mapping = self.config_manager.get_config("FIELD_DISPLAY_MAPPING", {})
            self.chart_colors = self.config_manager.get_config("CHART_COLORS", {})
            self.metric_fields = self.config_manager.get_config("METRIC_FIELDS", {})
            
        except Exception as e:
            self.logger.error(f"显示格式化配置加载失败: {e}")
            # 设置默认配置，确保服务可用
            self.field_display_mapping = {}
            self.chart_colors = {}
            self.metric_fields = {}
    
    def format_table_data(self, params: Dict[str, Any], records: List[Any], group_title: str) -> Dict[str, Any]:
        """
        将数据库查询结果转换为前端表格显示格式
        
        Args:
            params: 查询参数，包含metrics、grouping等信息
            records: 数据库查询结果记录
            group_title: 分组字段显示名称
            
        Returns:
            dict: 格式化后的表格数据
            {
                "data_rows": [],           # 表格数据行
                "dynamic_metrics": [],     # 动态指标配置
                "free_group_field": str,   # 分组字段名
                "records": [],             # 原始记录
                "advanced_conditions": str # 高级条件
            }
        """
        try:
            self.logger.info(f"开始格式化表格数据 - 分组: {group_title}, 记录数: {len(records)}")
            
            # 参数验证
            if not params or not records:
                raise ValueError("参数或记录不能为空")
            
            data_rows = []
            # 深拷贝metrics避免修改原始数据
            dynamic_metrics = [m.copy() for m in params['metrics']]
            
            # 确保所有metric都有正确的label和selected字段
            for metric in dynamic_metrics:
                # 确保label字段存在 - 使用配置管理器获取
                if 'label' not in metric:
                    metric['label'] = self.config_manager.get_report_field_display_name(metric['field'])
                
                if 'processed' in metric and 'valid_conditions' in metric['processed']:
                    # 数值型指标：将valid_conditions转为selected
                    metric['selected'] = [
                        cond['display_text'] 
                        for cond in metric['processed']['valid_conditions']
                        if cond.get('sql_ready', True)
                    ]
                elif 'selected' not in metric:
                    # 保底处理
                    metric['selected'] = []

            # grand_total计算逻辑
            grand_total = self._calculate_grand_total(dynamic_metrics, records)

            for rec in records:
                row: list = [getattr(rec, list(rec._fields)[0])]  # 第一列分组值
                
                for metric in dynamic_metrics:
                    field_type = self._get_field_type(metric['field'])
                    
                    if field_type in ('number', 'number_range'):
                        # 数值型字段处理
                        self._process_numeric_metric(row, metric, rec, grand_total)
                    else:
                        # 分类字段处理
                        self._process_categorical_metric(row, metric, rec, grand_total)
                
                row.append(rec.total_count)
                data_rows.append(row)

            # 合计行逻辑
            if data_rows:
                sum_row = self._calculate_sum_row(data_rows, grand_total)
                data_rows.append(sum_row)
            
            result = {
                "grouping_expr": params.get('grouping_expr'),
                "dynamic_metrics": dynamic_metrics,
                "data_rows": data_rows,
                "free_group_field": group_title,
                "records": records,
                "advanced_conditions": params.get('raw_params', {}).get('advanced_str', ''),
                "query": None
            }
            
            self.logger.info(f"表格数据格式化完成 - 数据行数: {len(result['data_rows'])}")
            return result
            
        except Exception as e:
            self.logger.error(f"表格数据格式化失败: {e}")
            # 返回空结果，确保前端不会崩溃
            return {
                "data_rows": [],
                "dynamic_metrics": [],
                "free_group_field": group_title,
                "records": records,
                "advanced_conditions": ""
            }
    
    def _calculate_grand_total(self, dynamic_metrics: List[Dict[str, Any]], records: List[Any]) -> int:
        """计算grand_total值"""
        # 检查是否有范围查询
        has_range_query = False
        for metric in dynamic_metrics:
            field_type = self._get_field_type(metric['field'])
            if field_type in ('number', 'number_range'):
                conditions = metric.get('processed', {}).get('valid_conditions', []) or metric.get('valid_conditions', [])
                if conditions:
                    has_range_query = True
                    break
        
        if has_range_query:
            # 范围查询：使用统计指标的总数作为grand_total
            grand_total = 0
            for metric in dynamic_metrics:
                field_type = self._get_field_type(metric['field'])
                if field_type in ('number', 'number_range'):
                    conditions = metric.get('processed', {}).get('valid_conditions', []) or metric.get('valid_conditions', [])
                    if conditions:
                        for val in metric['selected']:
                            # 计算该范围的总数
                            range_total = 0
                            for rec in records:
                                col_name = self._generate_column_name(metric['field'], val)
                                count = getattr(rec, col_name, 0) or 0
                                range_total += count
                            grand_total += range_total
            self.logger.debug(f"范围查询使用统计指标总数作为grand_total: {grand_total}")
        else:
            # 非范围查询：使用所有记录的总数
            grand_total = sum(rec.total_count for rec in records) if records else 1
            self.logger.debug(f"非范围查询使用所有记录总数作为grand_total: {grand_total}")
        
        return grand_total
    
    def _get_field_type(self, field_name: str) -> str:
        """获取字段类型"""
        metric_fields = self.config_manager.get_config("METRIC_FIELDS", {})
        return metric_fields.get(field_name, {}).get('type', 'category')
    
    def _generate_column_name(self, field_name: str, value: str) -> str:
        """生成列名"""
        if '-' in value:
            # 范围条件：从"min-max"提取min和max
            # 处理双连字符的情况，如"-0.75--0.5"
            if value.count('-') >= 2:
                # 双连字符情况：从第二个连字符开始分割
                parts = value.split('-', 2)  # 最多分割2次
                if len(parts) >= 3:
                    min_val = parts[0] + parts[1]  # 合并负号
                    max_val = parts[2]
                    col_name = f"{field_name}_{min_val}_{max_val}_count"
                else:
                    col_name = f"{field_name}_{value.replace('-', '_').replace(' ', '_')}_count"
            else:
                # 单连字符情况
                parts = value.split('-')
                if len(parts) == 2:
                    min_val = parts[0]
                    max_val = parts[1]
                    col_name = f"{field_name}_{min_val}_{max_val}_count"
                else:
                    col_name = f"{field_name}_{value.replace('-', '_').replace(' ', '_')}_count"
        elif '到' in value:
            # 范围条件：从"min 到 max"提取min和max
            parts = value.split(' 到 ')
            if len(parts) == 2:
                min_val = parts[0]
                max_val = parts[1]
                col_name = f"{field_name}_{min_val}_{max_val}_count"
            else:
                col_name = f"{field_name}_{value.replace('-', '_').replace(' ', '_')}_count"
        else:
            # 单值条件
            col_name = f"{field_name}_{value}_count"
        
        return col_name
    
    def _process_numeric_metric(self, row: List[Any], metric: Dict[str, Any], rec: Any, grand_total: int):
        """处理数值型指标"""
        field_type = self._get_field_type(metric['field'])
        conditions = metric.get('processed', {}).get('valid_conditions', []) or metric.get('valid_conditions', [])
        
        if conditions:
            # 有范围条件：按原有逻辑处理
            for val in metric['selected']:
                col_name = self._generate_column_name(metric['field'], val)
                count = getattr(rec, col_name, 0) or 0
                row.append(count)
                row.append(round(count / grand_total * 100, 2) if grand_total else 0)
        else:
            # 没有范围条件，显示默认统计值
            self.logger.debug(f"处理数值型字段 '{metric['field']}' 的默认统计值")
            
            # 添加默认统计列
            avg_val = getattr(rec, f"{metric['field']}_avg", 0) or 0
            max_val = getattr(rec, f"{metric['field']}_max", 0) or 0
            min_val = getattr(rec, f"{metric['field']}_min", 0) or 0
            count_val = getattr(rec, f"{metric['field']}_count", 0) or 0
            
            row.append(round(avg_val, 2))  # 平均值
            row.append(round(max_val, 2))  # 最大值
            row.append(round(min_val, 2))  # 最小值
            row.append(count_val)  # 有效数据数量
            
            # 尝试添加标准差
            try:
                std_val = getattr(rec, f"{metric['field']}_std", 0) or 0
                row.append(round(std_val, 2))
            except:
                pass  # 如果不支持标准差，跳过
    
    def _process_categorical_metric(self, row: List[Any], metric: Dict[str, Any], rec: Any, grand_total: int):
        """处理分类指标"""
        for val in metric['selected']:
            # 生成与_apply_metrics一致的列名
            col_name = f"{metric['field']}_{val.replace('-', '_')}_count"
            count = getattr(rec, col_name, 0) or 0
            row.append(count)
            row.append(round(count / grand_total * 100, 2) if grand_total else 0)
    
    def _calculate_sum_row(self, data_rows: List[List[Any]], grand_total: int) -> List[Any]:
        """计算合计行"""
        sum_row: list = ["合计"]
        page_total = sum(row[-1] for row in data_rows)
        
        for i in range(1, len(data_rows[0]) - 1, 2):
            sum_count = sum(row[i] for row in data_rows)
            sum_row.append(sum_count)
            sum_row.append(round(sum_count / grand_total * 100, 2) if grand_total else 0)
        sum_row.append(page_total)
        
        return sum_row
    
    def build_multi_level_header(self, dynamic_metrics: List[Dict[str, Any]], group_title: str) -> List[List[Dict[str, Any]]]:
        """
        构建报表的三层表头结构，包含合并单元格信息
        
        Args:
            dynamic_metrics: 动态指标配置列表
            group_title: 分组字段显示名称
            
        Returns:
            list: 三层表头结构
            [
                first_row,   # 第一行：分组字段 + 指标名称
                second_row,  # 第二行：指标子项
                third_row    # 第三行：数量/占比
            ]
        """
        try:
            self.logger.info(f"开始生成三层表头 - 分组: {group_title}, 指标数: {len(dynamic_metrics)}")
            
            header = []
            # 第一行：记录每个单元格的实际列起始位置
            first_row = []
            current_col = 1  # 列索引从1开始
            first_row.append({"text": group_title, "rowspan": 3, "start_col": current_col})
            current_col += 1  # 分组字段占1列

            for metric in dynamic_metrics:
                colspan = len(metric["selected"]) * 2
                first_row.append({
                    "text": metric["label"],
                    "colspan": colspan,
                    "start_col": current_col
                })
                current_col += colspan

            first_row.append({
                "text": "统计总数",
                "rowspan": 3,
                "start_col": current_col
            })
            header.append(first_row)

            # 第二行：动态计算子项起始列
            second_row = []
            current_col = 2  # 从分组字段后的第2列开始
            for metric in dynamic_metrics:
                for sub in metric["selected"]:
                    second_row.append({
                        "text": sub,
                        "colspan": 2,
                        "start_col": current_col
                    })
                    current_col += 2
            header.append(second_row)

            # 第三行：动态计算每个单元格的列起始位置
            third_row = []
            current_col = 2  # 从第2列开始（第1列是分组字段）
            for metric in dynamic_metrics:
                for _ in metric["selected"]:
                    third_row.append({"text": "数量", "start_col": current_col})
                    current_col += 1
                    third_row.append({"text": "占比", "start_col": current_col})
                    current_col += 1
            header.append(third_row)
            
            self.logger.info("三层表头生成完成")
            return header
            
        except Exception as e:
            self.logger.error(f"三层表头生成失败: {e}")
            # 返回空表头，确保前端不会崩溃
            return [[], [], []]
    
    def build_chart_data(self, data_rows: List[List[Any]], dynamic_metrics: List[Dict[str, Any]], chart_type: str = "bar") -> Dict[str, Any]:
        """
        将聚合数据转换为Chart.js所需的格式
        
        功能说明:
            将表格形式的聚合数据转换为Chart.js图表库所需的JSON格式。
            支持多种图表类型（柱状图、折线图、饼图），自动处理数据映射和颜色配置。
        
        数据转换逻辑:
            1. 数据行格式: [分组值, 指标1_count, 指标1_ratio, 指标2_count, 指标2_ratio, ..., 总记录数]
            2. 标签提取: 从每行第一列提取分组值作为X轴标签
            3. 数据集构建: 为每个指标值创建独立的数据集，包含数据数组和颜色配置
            4. 颜色映射: 根据CHART_COLORS配置为不同指标值分配颜色
        
        Args:
            data_rows: 表格数据行列表，每行包含分组值和各指标的数量/占比数据
            dynamic_metrics: 动态指标配置列表，包含指标字段、标签、选项值等信息
            chart_type: 图表类型，支持 "bar"(柱状图)、"line"(折线图)、"pie"(饼图)
            
        Returns:
            dict: Chart.js格式的数据对象
            {
                "labels": [str],        # X轴标签列表（分组值）
                "datasets": [dict],     # 数据集列表，每个数据集包含label、data、backgroundColor
                "chart_type": str,      # 图表类型
                "error": str            # 错误信息（仅在出错时）
            }
        
        错误处理:
            - 数据格式错误: 返回空图表数据并记录错误
            - 配置缺失: 使用默认颜色配置
            - 数据为空: 返回空图表结构
        """
        try:
            self.logger.info(f"开始转换图表数据 - 图表类型: {chart_type}, 数据行数: {len(data_rows)}, 指标数: {len(dynamic_metrics)}")
            
            # 参数验证
            if not isinstance(data_rows, list):
                raise ValueError(f"data_rows参数类型错误，期望list，实际{type(data_rows)}")
            
            if not isinstance(dynamic_metrics, list):
                raise ValueError(f"dynamic_metrics参数类型错误，期望list，实际{type(dynamic_metrics)}")
            
            # 获取图表颜色配置
            try:
                chart_colors = self.config_manager.get_config("CHART_COLORS", {})
                metrics_colors = chart_colors.get('METRICS', {})
                self.logger.debug(f"获取到图表颜色配置，指标颜色数: {len(metrics_colors)}")
            except Exception as color_error:
                self.logger.warning(f"获取图表颜色配置失败，使用默认颜色: {color_error}")
                metrics_colors = {}
            
            # 处理数据行格式 - 确保每行都是列表格式
            if data_rows and not isinstance(data_rows[0], (list, tuple)):
                self.logger.debug("数据行格式转换：将单值行转换为列表格式")
                data_rows = [[row] for row in data_rows]
            
            # 处理空数据情况
            if not data_rows:
                self.logger.warning("数据行为空，返回空图表数据")
                return {
                    "labels": [],
                    "datasets": [],
                    "chart_type": chart_type,
                    "message": "没有找到符合条件的数据"
                }
            
            # 构建标签（分组值）- 从每行第一列提取
            try:
                labels = []
                for row in data_rows:
                    if row and len(row) > 0:
                        # 跳过合计行，稍后单独处理
                        if str(row[0]).strip() != "合计":
                            labels.append(str(row[0]))
                
                # 检查最后一行是否为合计行
                if data_rows and len(data_rows) > 0:
                    last_row = data_rows[-1]
                    if last_row and len(last_row) > 0 and str(last_row[0]).strip() == "合计":
                        if "合计" not in labels:
                            labels.append("合计")
                
                self.logger.debug(f"提取到 {len(labels)} 个分组标签: {labels[:5]}...")  # 只显示前5个标签
                
            except Exception as label_error:
                self.logger.error(f"标签提取失败: {label_error}")
                raise ValueError(f"无法从数据行中提取标签: {label_error}")
            
            # 构建数据集 - 为每个指标值创建独立的数据集
            try:
                chart_datasets = []
                
                for i, metric in enumerate(dynamic_metrics):
                    # 获取指标标签
                    metric_label = metric.get("label", f"指标_{i}")
                    
                    # 获取指标的可选值（distinct_vals或selected）
                    enum_values = metric.get("distinct_vals") or metric.get("selected", [])
                    
                    if not enum_values:
                        self.logger.warning(f"指标 {metric_label} 没有可选值，跳过")
                        continue
                    
                    self.logger.debug(f"处理指标 {metric_label}，可选值: {enum_values}")
                    
                    # 为每个可选值创建数据集
                    for j, enum_val in enumerate(enum_values):
                        # 计算数据在行中的索引位置
                        # 数据格式: [分组值, 指标1_count, 指标1_ratio, 指标2_count, 指标2_ratio, ...]
                        idx_offset = 1 + 2 * (i * len(enum_values) + j)
                        
                        # 提取该指标值在所有分组中的数据
                        data = []
                        for row in data_rows:
                            if len(row) > idx_offset:
                                # 确保数据为数值类型
                                value = row[idx_offset]
                                if value is None:
                                    data.append(0)
                                else:
                                    try:
                                        data.append(float(value))
                                    except (ValueError, TypeError):
                                        data.append(0)
                            else:
                                data.append(0)
                        
                        # 获取该指标值的颜色
                        base_color = metrics_colors.get(enum_val, "#36A2EB")
                        
                        # 创建数据集对象
                        dataset = {
                            "label": f"{metric_label} - {enum_val}",
                            "data": data,
                            "backgroundColor": [base_color] * len(data),
                        }
                        
                        chart_datasets.append(dataset)
                        
                        self.logger.debug(f"创建数据集: {dataset['label']}, 数据点: {len(data)}")
                
                self.logger.info(f"成功创建 {len(chart_datasets)} 个数据集")
                
            except Exception as dataset_error:
                self.logger.error(f"数据集构建失败: {dataset_error}")
                raise ValueError(f"构建图表数据集时发生错误: {dataset_error}")
            
            # 构建最终图表数据
            chart_data = {
                "labels": labels,
                "datasets": chart_datasets,
                "chart_type": chart_type
            }
            
            self.logger.info(f"图表数据转换完成 - 标签数: {len(labels)}, 数据集数: {len(chart_datasets)}, 图表类型: {chart_type}")
            return chart_data
            
        except Exception as e:
            error_msg = f"图表数据转换失败: {str(e)}"
            self.logger.error(error_msg, exc_info=True)
            
            # 返回空图表数据，确保前端不会崩溃
            return {
                "labels": [],
                "datasets": [],
                "chart_type": chart_type,
                "error": error_msg
            }
    
    def export_to_excel(self, header: List[List[Dict[str, Any]]], data_rows: List[List[Any]], file_name: str) -> BytesIO:
        """
        将报表数据导出为Excel文件，包含格式设置
        
        Args:
            header: 三层表头结构
            data_rows: 表格数据行
            file_name: 导出文件名
            
        Returns:
            BytesIO: Excel文件流
        """
        try:
            self.logger.info(f"开始导出Excel文件 - 文件名: {file_name}, 数据行数: {len(data_rows)}")
            
            # 导入必要的模块
            from openpyxl import Workbook
            from openpyxl.styles import Alignment, numbers
            from io import BytesIO
            
            wb = Workbook()
            ws = wb.active
            
            # 确保ws不为None
            if ws is None:
                raise ValueError("无法创建工作表")

            # 1. 合并单元格
            for row_idx, row in enumerate(header, 1):
                for cell in row:
                    # 先写入值
                    ws.cell(row=row_idx, column=cell["start_col"], value=cell["text"])
                    
                    # 后处理合并
                    if "rowspan" in cell or "colspan" in cell:
                        end_row = row_idx + cell.get("rowspan", 1) - 1
                        end_col = cell["start_col"] + cell.get("colspan", 1) - 1
                        ws.merge_cells(
                            start_row=row_idx, end_row=end_row,
                            start_column=cell["start_col"], end_column=end_col
                        )
                    if "colspan" in cell and cell["colspan"] > 1:
                        start_col = cell.get("start_col", 1)
                        end_col = start_col + cell["colspan"] - 1
                        ws.merge_cells(
                            start_row=row_idx,
                            end_row=row_idx,
                            start_column=start_col,
                            end_column=end_col
                        )

            # 2. 写入所有表头单元格
            for row_idx, row in enumerate(header, start=1):
                for cell in row:
                    start_col = cell.get("start_col", 1)
                    ws.cell(row=row_idx, column=start_col, value=cell["text"])
                    ws.cell(row=row_idx, column=start_col).alignment = Alignment(
                        horizontal='center', vertical='center'
                    )

            # 3. 写入数据行并设置百分比格式
            for row_idx, row in enumerate(data_rows, start=len(header) + 1):
                total_columns = len(row)
                for col_idx, value in enumerate(row, start=1):
                    cell = ws.cell(row=row_idx, column=col_idx, value=value)
                    if col_idx >= 3 and col_idx < total_columns and (col_idx - 1) % 2 == 0:
                        cell.value = value / 100
                        cell.number_format = numbers.FORMAT_PERCENTAGE_00

            output = BytesIO()
            wb.save(output)
            output.seek(0)
            
            self.logger.info("Excel文件导出完成")
            return output
            
        except Exception as e:
            self.logger.error(f"Excel文件导出失败: {e}")
            # 返回空的文件流，确保前端不会崩溃
            return BytesIO() 